import sqlite3


class Product:
    def __init__(self, id, name, category, stock, sales):
        self.id = id
        self.name = name
        self.category = category
        self.stock = stock
        self.sales = sales


class ProductManager:
    def __init__(self):
        self.conn = sqlite3.connect('products.db')
        self.create_table()
        self.products = []

    def create_table(self):
        cursor = self.conn.cursor()
        cursor.execute('''CREATE TABLE IF NOT EXISTS products
                          (id INTEGER PRIMARY KEY,
                           name TEXT,
                           category TEXT,
                           stock INTEGER,
                           sales INTEGER)''')
        self.conn.commit()

    def add_product(self, product):
        cursor = self.conn.cursor()
        cursor.execute("INSERT INTO products VALUES (?, ?, ?, ?, ?)",
                       (product.id, product.name, product.category, product.stock, product.sales))
        self.conn.commit()
        self.products.append(product)

    def remove_product(self, product):
        cursor = self.conn.cursor()
        cursor.execute("DELETE FROM products WHERE id=?", (product.id,))
        self.conn.commit()
        self.products.remove(product)

    def update_product(self, product):
        cursor = self.conn.cursor()
        cursor.execute("UPDATE products SET name=?, category=?, stock=?, sales=? WHERE id=?",
                       (product.name, product.category, product.stock, product.sales, product.id))
        self.conn.commit()

    def search_product_by_name(self, name):
        return [p for p in self.products if p.name == name]

    def search_product_by_category(self, category):
        return [p for p in self.products if p.category == category]

    def search_low_stock_products(self):
        return [p for p in self.products if p.stock < 5]

    def sort_products_by_sales(self):
        return sorted(self.products, key=lambda p: p.sales)

    def get_sales_statistics(self):
        category_sales = {}
        total_sales = 0
        for p in self.products:
            if p.category in category_sales:
                category_sales[p.category] += p.sales
            else:
                category_sales[p.category] = p.sales
            total_sales += p.sales
        category_ratios = {k: v / total_sales for k, v in category_sales.items()}
        return {'category_sales': category_sales, 'category_ratios': category_ratios}

    def load_products(self):
        cursor = self.conn.cursor()
        cursor.execute('SELECT id, name, category, stock, sales FROM products')
        rows = cursor.fetchall()
        for row in rows:
            p = Product(row[0], row[1], row[2], row[3], row[4])
            self.products.append(p)

    def close(self):
        self.conn.close()


def print_menu():
    print('1. 添加商品')
    print('2. 删除商品')
    print('3. 修改商品')
    print('4. 查询商品')
    print('5. 按销售量排序')
    print('6. 显示销售统计')
    print('7. 退出程序')


def add_product_menu(product_manager):
    id = int(input('请输入商品编号：'))
    name = input('请输入商品名称：')

    category = input('请输入商品类别：')
    stock = int(input('请输入商品库存量：'))

    sales = int(input('请输入商品销售量：'))
    product = Product(id, name, category, stock, sales)
    product_manager.add_product(product)

    print('商品已添加')


def remove_product_menu(product_manager):
    id = int(input('请输入要删除的商品编号：'))
    for p in product_manager.products:

        if p.id == id:
            product_manager.remove_product(p)
            print('商品已删除')
            return
    print('未找到该商品')


def update_product_menu(product_manager):
    id = int(input('请输入要修改的商品编号：'))
    for p in product_manager.products:
        if p.id == id:
            name = input('请输入新的商品名称：')

            category = input('请输入新的商品类别：')

            stock = int(input('请输入新的商品库存量：'))

            sales = int(input('请输入新的商品销售量：'))

            p.name = name

            p.category = category

            p.stock = stock

            p.sales = sales

            product_manager.update_product(p)

            print('商品已修改')

            return
    print('未找到该商品')


def search_product_menu(product_manager):
    print('1. 按名称查询')

    print('2. 按类别查询')

    print('3. 查询低库存商品')
    choice = int(input('请选择查询方式：'))
    if choice == 1:
        name = input('请输入要查询的商品名称：')
        results = product_manager.search_product_by_name(name)
    elif choice == 2:

        category = input('请输入要查询的商品类别：')

        results = product_manager.search_product_by_category(category)
    elif choice == 3:
        results = product_manager.search_low_stock_products()
    if results:
        for p in results:
            print(f'编号：{p.id}，名称：{p.name}，类别：{p.category}，库存量：{p.stock}，销售量：{p.sales}')
    else:

        print('未找到符合条件的商品')


def sort_products_menu(product_manager):
    products = product_manager.sort_products_by_sales()
    for p in products:
        print(f'编号：{p.id}，名称：{p.name}，类别：{p.category}，库存量：{p.stock}，销售量：{p.sales}')


def sales_statistics_menu(product_manager):
    stats = product_manager.get_sales_statistics()

    print('各类商品销售量统计：')

    for category, sales in stats['category_sales'].items():
        print(f'{category}: {sales}')

    print('各类商品销售比例：')

    for category, ratio in stats['category_ratios'].items():
        print(f'{category}: {ratio:.2%}')


def main_menu():
    product_manager = ProductManager()

    product_manager.load_products()
    while True:
        print_menu()
        choice = int(input('请输入功能编号：'))

        if choice == 1:
            add_product_menu(product_manager)

        elif choice == 2:
            remove_product_menu(product_manager)

        elif choice == 3:
            update_product_menu(product_manager)

        elif choice == 4:
            search_product_menu(product_manager)

        elif choice == 5:
            sort_products_menu(product_manager)

        elif choice == 6:
            sales_statistics_menu(product_manager)

        elif choice == 7:
            product_manager.close()
            break
        else:
            print('无效的功能编号')


if __name__ == '__main__':
    main_menu()